package com.hsnn.medstgmini.sys.information.dao.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.StdHealthbureau;
import com.hsnn.medstgmini.model.MessageHasRead;
import com.hsnn.medstgmini.model.MessageType;
import com.hsnn.medstgmini.sys.information.dao.SysMessageDao;
import com.hsnn.medstgmini.sys.information.model.SysInfoAttachment;
import com.hsnn.medstgmini.sys.information.model.SysMessage;
import com.hsnn.medstgmini.sys.information.model.SysMessageRecp;
import com.hsnn.medstgmini.sys.model.UserType;
import com.hsnn.medstgmini.util.Pagination;
@Repository
public class SysMessageDaoImpl extends BaseHibernateDAO implements SysMessageDao {

	
	/**
	 * @category 新建消息
	 * @author 韩守松
	 * @date   2015年11月9日
	 * @param  @param sysMessage
	 */
	public void saveOrUpDate(SysMessage sysMessage) {
		getSession().clear();
		String dataType="";
		if(StringUtils.isNotBlank(sysMessage.getMessageId())){
			dataType = "编辑";
			Map<String,Object> map = updateMessage(sysMessage.getMessageId());
			sysMessage.setFatherId(map.get("fatherId")==null?"":(String)map.get("fatherId"));
			sysMessage.setAcctType(Integer.valueOf(map.get("acctType")+""));
			sysMessage.setOrgId(map.get("orgId")==null?"":(String)map.get("orgId"));
			sysMessage.setOrgName(map.get("orgName")==null?"":(String)map.get("orgName"));
			sysMessage.setSendUser(map.get("sendUser")==null?"":(String)map.get("sendUser"));
		}else{
			dataType = "新增";
			sysMessage.setMessageId(UUID.randomUUID().toString());
		}
		getSession().saveOrUpdate(sysMessage);
		getSession().flush();
		saveLog(sysMessage.getMessageId());
		
		if(StringUtils.isNotBlank(sysMessage.getSysinfo().getUrl())){
			if("编辑".equals(dataType)){
				deleteUrl(sysMessage.getMessageId());
			}
				String[] urls = sysMessage.getSysinfo().getUrl().split(",");
				String[] fileNames = sysMessage.getSysinfo().getFileName().split(",");
				for( int i=0;i<urls.length;i++ ){
					SysInfoAttachment sysinfo = new SysInfoAttachment();
					sysinfo.setSysInfoAttachId(UUID.randomUUID().toString());
					sysinfo.setSysInfoId(sysMessage.getMessageId());
					sysinfo.setUrl(urls[i]);
					sysinfo.setFileName(fileNames[i]);
					sysinfo.setDownloadCount(0);
					sysinfo.setUpdDatetime(new Date());
					sysinfo.setUpdUser(sysMessage.getUpdUser());
					getSession().saveOrUpdate(sysinfo);
					getSession().flush();
					
					sysInfoSaveLog(sysinfo.getSysInfoAttachId());
			}
			
			
		
		}
		
			if(StringUtils.isNotBlank(sysMessage.getReceUser()) 
					&& StringUtils.isNotBlank(sysMessage.getReceOrgId())
					&& StringUtils.isNotBlank(sysMessage.getReceType()) ){
				if("编辑".equals(dataType)){
					deleteRece(sysMessage.getMessageId());
				}
				//系统消息
				if(sysMessage.getType() == MessageType.XT.getKey()){
					saveAllRecpMessage(sysMessage.getUserId());
				}else{
					//用户消息
					String[] receId =  sysMessage.getReceUser().split(",");
					String[] orgId = sysMessage.getReceOrgId().split(",");
					String[] type = sysMessage.getReceType().split(",");
					if(receId.length > 0){
						for(int i=0;i<receId.length;i++){
							getSession().clear();
							SysMessageRecp receinfo = new SysMessageRecp();
							if(StringUtils.isNotBlank(receId[i])){
								receinfo.setReceUser(receId[i]);
							}
							if(StringUtils.isNotBlank(orgId[i])){
								receinfo.setRecOrgId(orgId[i]);
							}
							if(StringUtils.isNotBlank(receId[i]) && StringUtils.isNotBlank(orgId[i])){
								receinfo.setRecOrgName(getOrgNameByOrgId(orgId[i],type[i]));
							}
							receinfo.setMessageId(sysMessage.getMessageId());
							receinfo.setUpdDatetime(sysMessage.getUpdDatetime());
							receinfo.setUpdUser(sysMessage.getUpdUser());
							addReceUser(receinfo);
							receinfoSaveLog(receinfo.getMessageId(),receinfo.getReceUser());
						}
					}
			}
			
		}
		
	}
	
	@SuppressWarnings("unchecked")
	private Map<String,Object> updateMessage(String messageId) {
		List<SysMessage> list = getSysMessageById(messageId);
		Map<String,Object>  map = new HashMap<String,Object>();
		map = (Map<String,Object>)list.get(0);
		return map;
	}

	/**
	 * @category 获取收件箱消息
	 * @author 韩守松
	 * @date   2015年11月9日
	 * @param  @param sysMessage
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public  Pagination getSysMessageReceList(Pagination page,SysMessage sysMessage) {
		StringBuffer hql = new  StringBuffer();
		StringBuffer hqlCount = new  StringBuffer();
		
		hqlCount.append("select count(*) from  sys_message_recp smr   left join sys_message sm  on  smr.message_id = sm.message_id    where sm.status = 1 and smr.rece_user = :receUser ");
		
	
		hql.append(" select sm.message_id messageId,sm.father_id fatherId,sm.acct_type acctType, ");
		hql.append(" sm.org_id orgId,sm.org_name orgName,sm.group_id groupId,sm.send_user sendUser, ");
		hql.append(" smr.rece_user receUser,sm.title,sm.content,sm.type,sm.status,sm.delivery_time deliveryTime, ");
		hql.append(" sm.send_datetime sendDatetime,sm.read_datetime readDatetime, ");
		hql.append(" sm.has_read hasRead,sm.upd_user updUser,sm.upd_datetime updDatetime ");
		hql.append(" from  sys_message_recp smr ");
		hql.append(" left join sys_message sm  on  smr.message_id = sm.message_id ");
		hql.append(" where sm.status = 1 and smr.rece_user = :receUser ");
			
		
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			hqlCount.append(" and sm.title like :title ");
			hql.append(" and sm.title like :title ");
		}
		if(null != sysMessage.getType()){
			hqlCount.append(" and sm.type = :type ");
			hql.append(" and sm.type = :type ");
		}
		hql.append(" order by sm.upd_datetime desc ");
		SQLQuery queryCount = getSession().createSQLQuery(hqlCount.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			queryCount.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			queryCount.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			queryCount.setString("receUser", sysMessage.getUserId());
		}
		page.setRecords(Long.valueOf(queryCount.list().get(0).toString()));
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			query.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			query.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			query.setString("receUser", sysMessage.getUserId());
		}
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		List<Map<String,Object>> list  = query.list();
		if(list.size() > 0){
			for(int i = 0;i<list.size();i++){
				list.get(i).put("type",MessageType.getName(list.get(i).get("type")==null?null:Integer.valueOf(list.get(i).get("type").toString())));
				list.get(i).put("hasRead",MessageHasRead.getName(list.get(i).get("hasRead")==null?null:Integer.valueOf(list.get(i).get("hasRead").toString())));
			}
		}
		page.setRows(list);
		return page;
	}
	
	/**
	 * @category 获取草稿箱消息
	 * @author 韩守松
	 * @date   2015年11月9日
	 * @param  @param sysMessage
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public  Pagination getSysMessageList(Pagination page,SysMessage sysMessage) {
		StringBuffer hql = new  StringBuffer();
		StringBuffer hqlCount = new  StringBuffer();
		
		hqlCount.append("select count(*) from sys_message sm  where sm.send_user = :sendUser and sm.status = 0  ");
		
		hql.append(" select sm.message_id messageId,sm.father_id fatherId,sm.acct_type acctType, ");
		hql.append(" sm.org_id orgId,sm.org_name orgName,sm.group_id groupId,sm.send_user sendUser, ");
		hql.append(" sm.title,sm.content,sm.type,sm.status,sm.delivery_time deliveryTime, ");
		hql.append(" sm.send_datetime sendDatetime,sm.read_datetime readDatetime, ");
		hql.append(" sm.has_read hasRead,sm.upd_user updUser,sm.upd_datetime updDatetime ");
		hql.append(" from sys_message sm  ");
		hql.append(" where  sm.send_user = :sendUser and sm.status = 0  ");

		
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			hqlCount.append(" and sm.title like :title ");
			hql.append(" and sm.title like :title ");
		}
		if(null != sysMessage.getType()){
			hqlCount.append(" and sm.type = :type ");
			hql.append(" and sm.type = :type ");
		}
		hql.append(" group by sm.message_id ");
		hql.append(" order by sm.upd_datetime desc ");
		SQLQuery queryCount = getSession().createSQLQuery(hqlCount.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			queryCount.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			queryCount.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			queryCount.setString("sendUser", sysMessage.getUserId());
		}
		page.setRecords(Long.valueOf(queryCount.list().get(0).toString()));
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			query.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			query.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			query.setString("sendUser", sysMessage.getUserId());
		}
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		List<Map<String,Object>> list  = query.list();
		if(list.size() > 0){
			for(int i = 0;i<list.size();i++){
				list.get(i).put("type",MessageType.getName(list.get(i).get("type")==null?null:Integer.valueOf(list.get(i).get("type").toString())));
			}
		}
		page.setRows(list);
		return page;
	}
	
	/**
	 * @category 获取发件箱消息
	 * @author 韩守松
	 * @date   2015年11月9日
	 * @param  @param sysMessage
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public  Pagination getSysMessageSendList(Pagination page,SysMessage sysMessage) {
		StringBuffer hql = new  StringBuffer();
		StringBuffer hqlCount = new  StringBuffer();
		
		hqlCount.append("select count(*) from sys_message sm left join sys_message_recp smr on  sm.message_id = smr.message_id where sm.send_user = :sendUser and sm.status = 1  ");
		
		hql.append(" select sm.message_id messageId,sm.father_id fatherId,sm.acct_type acctType, ");
		hql.append(" sm.org_id orgId,sm.org_name orgName,sm.group_id groupId,sm.send_user sendUser, ");
		hql.append(" smr.rece_user receUser,sm.title,sm.content,sm.type,sm.status,sm.delivery_time deliveryTime, ");
		hql.append(" sm.send_datetime sendDatetime,sm.read_datetime readDatetime, ");
		hql.append(" sm.has_read hasRead,sm.upd_user updUser,sm.upd_datetime updDatetime ");
		hql.append(" from sys_message sm  ");
		hql.append(" left join sys_message_recp smr on  sm.message_id = smr.message_id ");
		hql.append(" where  sm.send_user = :sendUser and sm.status = 1  ");
		
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			hqlCount.append(" and sm.title like :title ");
			hql.append(" and sm.title like :title ");
		}
		if(null != sysMessage.getType()){
			hqlCount.append(" and sm.type = :type ");
			hql.append(" and sm.type = :type ");
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			hqlCount.append(" and sm.send_user = :sendUser ");
			hql.append(" and sm.send_user = :sendUser ");
		}
		hql.append(" group by sm.message_id ");
		hql.append(" order by sm.upd_datetime desc ");
		SQLQuery queryCount = getSession().createSQLQuery(hqlCount.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			queryCount.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			queryCount.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			queryCount.setString("sendUser", sysMessage.getUserId());
		}
		page.setRecords(Long.valueOf(queryCount.list().get(0).toString()));
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		if(StringUtils.isNotBlank(sysMessage.getTitle())){
			query.setString("title", "%"+sysMessage.getTitle()+"%");
		}
		if(null != sysMessage.getType()){
			query.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			query.setString("sendUser", sysMessage.getUserId());
		}
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		List<Map<String,Object>> list  = query.list();
		if(list.size() > 0){
			for(int i = 0;i<list.size();i++){
				 list.get(i).put("type",MessageType.getName(list.get(i).get("type")==null?null:Integer.valueOf(list.get(i).get("type").toString())));
			}
		}
		page.setRows(list);
		return page;
	}
	/**
	 * @category 根据消息id获取消息信息
	 * @author 韩守松
	 * @date   2015年11月10日
	 * @param  @param messageId
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SysMessage> getSysMessageById(String messageId){
		
		StringBuffer hql = new StringBuffer();
		hql.append(" select sm.message_id messageId,sm.father_id fatherId,sufid.name fatherName,sm.acct_type acctType, ");
		hql.append(" sm.org_id orgId,sm.org_name orgName,sm.group_id groupId,sm.send_user sendUser,susend.name sendName, ");
		hql.append(" sm.title,sm.content,sm.type,sm.status,sm.delivery_time deliveryTime, ");
		hql.append(" sm.send_datetime sendDatetime,sm.read_datetime readDatetime, ");
		hql.append(" sm.has_read hasRead,sm.upd_user updUser,sm.upd_datetime updDatetime ");
		hql.append(" from sys_message sm  ");
		hql.append(" left join sys_user susend on  sm.send_user = susend.id ");
		hql.append(" left join sys_user sufid on  sm.father_id = sufid.id ");
		hql.append(" where sm.message_id = '").append(messageId).append("'");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	public void saveLog(String id) {
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_message_log  ")
		      .append(" select UUID(),sr.* from sys_message sr where sr.message_id= :id ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("id", id);
		queryLog.executeUpdate();
		getSession().flush();
	}
	/**
	 * @category 消息附件日志保存
	 * @author 韩守松
	 * @date   2015年11月11日
	 * @param  @param id
	 */
	public void sysInfoSaveLog(String id) {
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_info_attachment_log  ")
		      .append(" select UUID(),sr.* from sys_info_attachment sr where sr.sys_info_attach_id= :id ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("id", id);
		queryLog.executeUpdate();
		getSession().flush();
	}
	
	public void addReceUser(SysMessageRecp receinfo){
		getSession().clear();
		StringBuffer hql = new StringBuffer();
		hql.append(" insert into sys_message_recp(message_id,rece_user,rec_org_id,rec_org_name,status,upd_user,upd_datetime) ");
		hql.append(" value(:messageId,:receUser,:recOrgId,:recOrgName,:status,:updUsr,:updDatetime ) ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("messageId", receinfo.getMessageId());
		query.setString("receUser", receinfo.getReceUser());
		query.setString("recOrgId", receinfo.getRecOrgId());
		query.setString("recOrgName", receinfo.getRecOrgName());
		query.setInteger("status", 1);
		query.setString("updUsr", receinfo.getUpdUser());
		query.setDate("updDatetime", receinfo.getUpdDatetime());
		query.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 消息接收人日志保存
	 * @author 韩守松
	 * @date   2015年11月11日
	 * @param  @param id
	 */
	public void receinfoSaveLog(String id,String receUser) {
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_message_recp_log  ")
		      .append(" select UUID(),sr.* from sys_message_recp sr where sr.message_id= :id and sr.rece_user = :receUser ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("id", id);
		queryLog.setString("receUser", receUser);
		queryLog.executeUpdate();
		getSession().flush();
	}
	/**
	 * @category 根据消息id修改消息状态
	 * @author 韩守松
	 * @date   2015年11月11日
	 * @param  @param sysMessage
	 */
	public void update(SysMessage sysMessage){
		StringBuffer hql = new StringBuffer();
		hql.append("update sys_message set status = :status,send_datetime = :sendDatetime  where message_id = :messageId ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setInteger("status", sysMessage.getStatus());
		query.setDate("sendDatetime", new Date());
		query.setString("messageId", sysMessage.getMessageId());
		query.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 根据消息id修改消息阅读状态
	 * @author 韩守松
	 * @date   2015年11月11日
	 * @param  @param sysMessage
	 */
	public void updateReadStatus(SysMessage sysMessage){
		StringBuffer hql = new StringBuffer();
		hql.append("update sys_message set has_read = :hasRead,read_datetime = :readDatetime where message_id = :messageId ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setInteger("hasRead", sysMessage.getHasRead());
		query.setDate("readDatetime", sysMessage.getReadDatetime());
		query.setString("messageId", sysMessage.getMessageId());
		query.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 根据机构id获取机构名称
	 * @author 韩守松
	 * @date   2015年11月12日
	 * @param  @param OrgId
	 * @param  @return
	 */
	public String getOrgNameByOrgId(String OrgId,String type){
		StringBuffer hql = new StringBuffer();
		if((UserType.zx.getKey()+"").equals(type) || (UserType.hszx.getKey()+"").equals(type)){
			hql.append("select hea_bur_name name from std_healthbureau where hea_bur_id = :OrgId");
		}
		if((UserType.sc.getKey()+"").equals(type) || (UserType.ps.getKey()+"").equals(type)){
			hql.append("select comp_name name from std_compinfo where comp_id = :OrgId ");
		}
		if((UserType.yy.getKey()+"").equals(type)){
			hql.append("select hosp_name name from std_hospinfo where hosp_id = :OrgId ");
		}
		if((UserType.zj.getKey()+"").equals(type)){
			hql.append(" select name from std_expert where expert_id = :OrgId ");
		}
		
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("OrgId", OrgId);
		return (String)query.uniqueResult();
	}
	/**
	 * @category 根据消息id 获取附件表中的url
	 * @author 韩守松
	 * @date   2015年11月12日
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysInfoAttachment> getAllUrlsById(String messageId){
		StringBuffer hql = new StringBuffer();
		hql.append(" select sia.sys_info_attach_id sysInfoAttachId,sia.url,sia.file_name fileName ");
		hql.append(" from sys_info_attachment sia  ");
		hql.append(" where sia.sys_info_id = '").append(messageId).append("'");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	/**
	 * @category 根据消息id 获取接收人表中的接收人信息
	 * @author 韩守松
	 * @date   2015年11月12日
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysMessageRecp> getAllReceUserById(String messageId){
		StringBuffer hql = new StringBuffer();
		hql.append(" select smr.rece_user receUser,su.name receName,su.type receType,smr.rec_org_id recOrgId,smr.rec_org_name recOrgName ");
		hql.append(" from sys_message_recp smr  ");
		hql.append(" left join sys_user su on  smr.rece_user = su.id ");
		hql.append(" where smr.message_id = '").append(messageId).append("'");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 根据消息id删除相关接收人
	 * @author 韩守松
	 * @date   2015年11月12日
	 * @param  @param messageId
	 * @param  @return
	 */
	public void deleteRece(String messageId){
		StringBuffer hql = new StringBuffer();
		hql.append("delete from sys_message_recp where message_id = :messageId ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("messageId", messageId);
		query.executeUpdate();
		getSession().flush();
	}
	/**
	 * @category 根据消息id删除相关附件
	 * @author 韩守松
	 * @date   2015年11月12日
	 * @param  @param messageId
	 * @param  @return
	 */
	public void deleteUrl(String messageId){
		StringBuffer hql = new StringBuffer();
		hql.append("delete from sys_info_attachment where sys_info_id = :messageId ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("messageId", messageId);
		query.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 获取系统消息
	 * @author 韩守松
	 * @date   2015年11月9日
	 * @param  @param sysMessage
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public  List<SysMessage> getSysMessageListByType(Pagination page,SysMessage sysMessage) {
		StringBuffer hql = new  StringBuffer();
		hql.append(" select sm.message_id messageId,sm.father_id fatherId,sm.acct_type acctType, ");
		hql.append(" sm.org_id orgId,sm.org_name orgName,sm.group_id groupId,sm.send_user sendUser, ");
		hql.append(" smr.rece_user receUser,sm.title,sm.content,case sm.type when 1 then '系统' else '用户' end type,sm.status,sm.delivery_time deliveryTime, ");
		hql.append(" DATE_FORMAT(sm.send_datetime,'%Y-%m-%d') sendDatetime,DATE_FORMAT(sm.read_datetime,'%Y-%m-%d %H:%i:%S') readDatetime, ");
		hql.append(" case sm.has_read when 0 then '未阅读' else '已阅读' end hasRead,sm.upd_user updUser,DATE_FORMAT(sm.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime ");
		hql.append(" from sys_message sm  ");
		hql.append(" left join sys_info_attachment sia on  sm.message_id = sia.sys_info_id   ");
		hql.append(" right join sys_message_recp smr on  sm.message_id = smr.message_id   ");
		hql.append(" where sm.status = 1 and smr.rece_user = :receUser ");
		if(null != sysMessage.getType()){
			hql.append(" and sm.type = :type ");
		}
		hql.append(" order by sm.upd_datetime desc ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		if(null != sysMessage.getType()){
			query.setInteger("type", sysMessage.getType());
			
		}
		if(StringUtils.isNotBlank(sysMessage.getUserId())){
			query.setString("receUser", sysMessage.getUserId());
		}
		
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	/**
	 * @category 更新附件下载次数
	 * @author 韩守松
	 * @date   2015年11月17日
	 * @param  @param sysInfoAttachId
	 */
	public void updateDownSum(String sysInfoAttachId,String userId){
		StringBuffer hql = new StringBuffer();
		hql.append("update sys_info_attachment set download_count = download_count + 1,upd_datetime = :updDatetime,upd_user = :updUser where sys_info_attach_id = :sysInfoAttachId ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("updUser",userId);
		query.setDate("updDatetime", new Date());
		query.setString("sysInfoAttachId", sysInfoAttachId);
		query.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 保存系统发送信息给所有用户
	 * @author 韩守松
	 * @date   2015年11月18日
	 * @param  @param userId
	 */
	@SuppressWarnings("unchecked")
	public void saveAllRecpMessage(String userId){
		List<StdHealthbureau> listUser = getAllUser(userId);
		if(listUser.size() > 0){
			for(int i=0;i<listUser.size();i++){
				getSession().clear();
				Map<String,Object> map = new HashMap<String, Object>();
				map = (Map<String, Object>) listUser.get(i);
				StringBuffer hql = new StringBuffer();
				hql.append("insert into sys_message_recp(message_id,rece_user,rec_org_id,status,upd_user,upd_datetime)  ")
			      .append(" values( :uuid,:id,:orgId,1,:updUser,:updDatetime)  ");
			Query query = getSession().createSQLQuery(hql.toString());
			query.setString("uuid", UUID.randomUUID().toString());
			query.setString("id", map.get("id").toString());
			query.setString("orgId", map.get("orgId").toString());
			query.setString("updUser", userId);
			query.setDate("updDatetime", new Date());
			query.executeUpdate();
			getSession().flush();
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<StdHealthbureau> getAllUser(String userId){
		StringBuffer hql = new StringBuffer();
		hql.append(" select su.id,su.org_id orgId from sys_user su where su.id != :userId  ");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setString("userId", userId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
}
